﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Com.LongER.Framework.Utils;
//using Com.LongER.Business.ZCH001.ZCH001DSTableAdapters;
using System.Data.SqlClient;
using System.Data;
using System.Collections;
using Com.LongER.Framework.Utils.LogUtils;

namespace Com.LongER.Business.ZCH001
{
    public class CZCH001 : ABussinessBase
    {
        private Log log = Log.GetInstance();
        /// <summary>
        /// 
        /// </summary>
        /// <param name="htParam"></param>
        /// <returns></returns>
        public ZCH001DS.T_DIAODUDataTable GetT_DIAODU(Dictionary<string, string> dicParam)
        {
            string strDLNO = dicParam["DLNO"];   //调度指示号
            string strPINMING_CD = dicParam["PINMING_CD"];   //品名
            string strSHIJI_STA = dicParam["SHIJI_STA"];    //指示状态
            string strCHEPAI_NUM = dicParam["CHEPAI_NUM"]; //车牌号
            string strCHEZHU = dicParam["CHEZHU"];         //车主
            string strCHELIANG_XINGTAI = dicParam["CHELIANG_XINGTAI"];       //车辆形态
            string strHETONG_BIANHAO = dicParam["HETONG_BIANHAO"];//合同编号
            string strONO = dicParam["ONO"];  //订单ONO

            string strZHUANGHUO = dicParam["ZHUANGHUO"];  //装货地
            string strXIEHUO = dicParam["XIEHUO"];        //卸货地

            string strBETWEEN_START = ((int.Parse(dicParam["PAGE_NO"]) - 1) * 5 + 1).ToString();
            string strBETWEEN_END = (int.Parse(dicParam["PAGE_NO"]) * 5).ToString();

            ////DB接続文字列取得
            var connectionString = ConnectionStringUtil.GetConnectionString();
            SqlConnection con = new SqlConnection(connectionString);
            con.Open();
            string cnnstr = string.Empty;

            #region sqlContent
            // C#コード自動作成
            StringBuilder objSqlContent = new StringBuilder();
            objSqlContent.Append(" SELECT                                                                          ");
            objSqlContent.Append("     *                                                                           ");
            objSqlContent.Append(" FROM                                                                            ");
            objSqlContent.Append("     (                                                                           ");
            objSqlContent.Append("         SELECT                                                                  ");
            objSqlContent.Append("             DH.DLNO    AS DLNO                                                  ");
            objSqlContent.Append("             ,ISNULL(DM.SUB_NO,'') AS SUB_NO                                     ");
            objSqlContent.Append("             ,ISNULL(DH.FAHUO_YUDING_DAY,'') AS FAHUO_YUDING_DAY                 ");
            objSqlContent.Append("             ,ISNULL(DH.FAHUO_YUDING_TIME,'')  AS FAHUO_YUDING_TIME              ");
            objSqlContent.Append("             ,ISNULL(DM.PINMING_CD,'') AS PINMING_CD                             ");
            objSqlContent.Append("             ,ISNULL(PINM.CODE1_HANZI,'') AS PINMING_HANZI                       ");
            objSqlContent.Append("             ,ISNULL(DH.SHIJI_STA,'') AS SHIJI_STA                               ");
            objSqlContent.Append("             ,ISNULL(ZSZT.CODE1_HANZI,'') AS SHIJI_STA_HANZI                     ");
            objSqlContent.Append("             ,ISNULL(DH.CHEPAI_NUM,'') AS CHEPAI_NUM                             ");
            objSqlContent.Append("             ,ISNULL(DH.CHEZHU,'')  AS CHEZHU                                    ");
            objSqlContent.Append("             ,ISNULL(DH.CHEZHU_NAME,'')  AS CHEZHU_NAME                          ");
            objSqlContent.Append("             ,ISNULL(DH.CHELIANG_XINGTAI,'') AS CHELIANG_XINGTAI                 ");
            objSqlContent.Append("             ,ISNULL(CLXT.CODE1_HANZI,'') AS CHELIANG_XINGTAI_HANZI              ");
            objSqlContent.Append("             ,ISNULL(DH.HETONG_BIANHAO,'') AS HETONG_BIANHAO                     ");
            objSqlContent.Append("             ,ISNULL(DM.ONO,'') AS ONO                                           ");
            objSqlContent.Append("             ,ISNULL(DM.ZHUANGHUO,'') AS ZHUANGHUO                               ");
            objSqlContent.Append("             ,ISNULL(OD.ZHUANGHUO_LUOCHENG,'') AS ZHUANGHUO_NAME                 ");
            objSqlContent.Append("             ,ISNULL(DM.XIEHUO,'') AS XIEHUO                                     ");
            objSqlContent.Append("             ,ISNULL(OD.XIEHUO_LUOCHENG,'') AS XIEHUO_NAME                       ");
            objSqlContent.Append("             ,ISNULL(DH.DIAODU_COUNTS,0)  AS DIAODU_COUNTS                       ");
            objSqlContent.Append("             ,ISNULL(DH.DIAODU_WEIGHT,0) AS DIAODU_WEIGHT                        ");
            objSqlContent.Append("             ,ISNULL(DH.HETONG_YUNFEI,0)  AS  HETONG_YUNFEI                      ");
            objSqlContent.Append("             ,ISNULL(DH.BEIZHU,'')  AS BEIZHU                                    ");
            objSqlContent.Append("             ,rn = ROW_NUMBER                                                    ");
            objSqlContent.Append("             (                                                                   ");
            objSqlContent.Append("             )                                                                   ");
            objSqlContent.Append("             OVER                                                                ");
            objSqlContent.Append("             (                                                                   ");
            objSqlContent.Append("             ORDER BY                                                            ");
            objSqlContent.Append("                 DH.DLNO ASC ,                                                   ");
            objSqlContent.Append("                 DM.SUB_NO ASC                                                   ");
            objSqlContent.Append("             )                                                                   ");
            objSqlContent.Append("         FROM                                                                    ");
            objSqlContent.Append("             T_DIAODU_H DH                                                       ");
            objSqlContent.Append("             LEFT JOIN                                                           ");
            objSqlContent.Append("             T_DIAODU_M DM                                                       ");//调度明细
            objSqlContent.Append("             ON   DH.DLNO = DM.DLNO                                              ");
            objSqlContent.Append("             LEFT JOIN                                                           ");
            objSqlContent.Append("             T_ORDER OD                                                          ");//订单
            objSqlContent.Append("             ON   DM.ONO = OD.ONO                                                ");
            objSqlContent.Append("             LEFT JOIN                                                           ");
            objSqlContent.Append("             M_TONGYONG CLXT                                                     ");//车辆形态
            objSqlContent.Append("             ON DH.CHELIANG_XINGTAI = CLXT.KEY1                                  ");
            objSqlContent.Append("             AND CLXT.CODE_CD = 'CLXT'                                           ");
            objSqlContent.Append("             LEFT JOIN                                                           ");
            objSqlContent.Append("             M_TONGYONG PINM                                                     ");//品名
            objSqlContent.Append("             ON  DM.PINMING_CD = PINM.KEY1                                       ");
            objSqlContent.Append("             AND PINM.CODE_CD = 'PINM'                                           ");
            objSqlContent.Append("             LEFT JOIN                                                           ");
            objSqlContent.Append("             M_TONGYONG ZSZT                                                     ");//调度指示状态
            objSqlContent.Append("             ON  DH.SHIJI_STA = ZSZT.KEY1                                        ");
            objSqlContent.Append("             AND ZSZT.CODE_CD = 'ZSZT'                                           ");
            //objSqlContent.Append("             LEFT JOIN                                                           ");
            //objSqlContent.Append("             M_TONGYONG CLXT                                                     ");
            //objSqlContent.Append("             ON DH.CHELIANG_XINGTAI = CLXT.KEY1                                  "); //车辆形态
            //objSqlContent.Append("             AND CLXT.CODE_CD = 'CLXT'                                           ");
            objSqlContent.Append("         WHERE                                                                   ");
            objSqlContent.Append("                 1 = 1                                                           ");
            if (!String.IsNullOrWhiteSpace(strDLNO))
                objSqlContent.Append("             AND DH.DLNO = @DLNO                                  ");
            if (!String.IsNullOrWhiteSpace(strPINMING_CD))
                objSqlContent.Append("             AND DH.PINMING_CD = @PINMING_CD                                ");
            if (!String.IsNullOrWhiteSpace(strSHIJI_STA))
                objSqlContent.Append("             AND DH.SHIJI_STA = @SHIJI_STA                          ");
            if (!String.IsNullOrWhiteSpace(strCHEPAI_NUM))
                objSqlContent.Append("             AND DH.CHEPAI_NUM = @CHEPAI_NUM                        ");

            if (!String.IsNullOrWhiteSpace(strCHEZHU))
                objSqlContent.Append("             AND DH.CHEZHU = @CHEZHU                                  ");
            if (!String.IsNullOrWhiteSpace(strCHELIANG_XINGTAI))
                objSqlContent.Append("             AND DH.CHELIANG_XINGTAI = @CHELIANG_XINGTAI                     ");
            if (!String.IsNullOrWhiteSpace(strHETONG_BIANHAO))
                objSqlContent.Append("             AND DH.HETONG_BIANHAO = @HETONG_BIANHAO                   ");
            if (!String.IsNullOrWhiteSpace(strONO))
                objSqlContent.Append("             AND DM.ONO = @ONO                                               ");

            if (!String.IsNullOrWhiteSpace(strZHUANGHUO))
                objSqlContent.Append("             AND DM.ZHUANGHUO = @ZHUANGHUO                                   ");
            if (!String.IsNullOrWhiteSpace(strXIEHUO))
                objSqlContent.Append("             AND DM.XIEHUO = @XIEHUO                                         ");
            objSqlContent.Append("     )                                                                           ");
            objSqlContent.Append("     A                                                                           ");
            objSqlContent.Append(" WHERE                                                                           ");
            objSqlContent.Append("         A.rn BETWEEN @BETWEEN_START AND @BETWEEN_END;                           ");
            #endregion

            cnnstr = objSqlContent.ToString();

            log.Debug("CZCH001:" + cnnstr);

            SqlCommand cmd = new SqlCommand(cnnstr, con);
            cmd.CommandText = cnnstr;
            cmd.CommandType = System.Data.CommandType.Text;
            //调度指示号
            if (!String.IsNullOrWhiteSpace(strDLNO))
                cmd.Parameters.Add(getSqlParameter("DLNO", SqlDbType.Char, strDLNO));
            //品名
            if (!String.IsNullOrWhiteSpace(strPINMING_CD))
                cmd.Parameters.Add(getSqlParameter("PINMING_CD", SqlDbType.Char, strPINMING_CD));
            //指示状态
            if (!String.IsNullOrWhiteSpace(strSHIJI_STA))
                cmd.Parameters.Add(getSqlParameter("SHIJI_STA", SqlDbType.Char, strSHIJI_STA));
            //车牌号
            if (!String.IsNullOrWhiteSpace(strCHEPAI_NUM))
                cmd.Parameters.Add(getSqlParameter("CHEPAI_NUM", SqlDbType.Char, strCHEPAI_NUM));

            //车主
            if (!String.IsNullOrWhiteSpace(strCHEZHU))
                cmd.Parameters.Add(getSqlParameter("CHEZHU", SqlDbType.Char, strCHEZHU));
            //车辆形态
            if (!String.IsNullOrWhiteSpace(strCHELIANG_XINGTAI))
                cmd.Parameters.Add(getSqlParameter("CHELIANG_XINGTAI", SqlDbType.Char, strCHELIANG_XINGTAI));
            //合同编号
            if (!String.IsNullOrWhiteSpace(strHETONG_BIANHAO))
                cmd.Parameters.Add(getSqlParameter("HETONG_BIANHAO", SqlDbType.Char, strHETONG_BIANHAO));
            //订单ONO
            if (!String.IsNullOrWhiteSpace(strONO))
                cmd.Parameters.Add(getSqlParameter("ONO", SqlDbType.Char, strONO));
            //装货地
            if (!String.IsNullOrWhiteSpace(strZHUANGHUO))
                cmd.Parameters.Add(getSqlParameter("ZHUANGHUO", SqlDbType.Char, strZHUANGHUO));
            //卸货地
            if (!String.IsNullOrWhiteSpace(strXIEHUO))
                cmd.Parameters.Add(getSqlParameter("XIEHUO", SqlDbType.Char, strXIEHUO));

            if (!String.IsNullOrWhiteSpace(strBETWEEN_START))
                cmd.Parameters.Add(getSqlParameter("BETWEEN_START", SqlDbType.Int, int.Parse(strBETWEEN_START)));
            //
            if (!String.IsNullOrWhiteSpace(strBETWEEN_END))
                cmd.Parameters.Add(getSqlParameter("BETWEEN_END", SqlDbType.Int, int.Parse(strBETWEEN_END)));

            SqlDataReader odr = cmd.ExecuteReader();

            ZCH001DS.T_DIAODUDataTable tb = new ZCH001DS.T_DIAODUDataTable();
            if (odr.HasRows)
            {
                // データ有り
                while (odr.Read())
                {
                    ZCH001DS.T_DIAODURow dr = tb.NewT_DIAODURow();
                    //row 1
                    dr.DLNO = odr["DLNO"].ToString();                   //调度指示号
                    dr.SUB_NO = odr["SUB_NO"].ToString();               //调度指示子号
                    dr.FAHUO_YUDING_DAY = odr["FAHUO_YUDING_DAY"].ToString();  //发货预定日
                    dr.FAHUO_YUDING_TIME = odr["FAHUO_YUDING_TIME"].ToString();//发货预定时间
                    dr.PINMING_CD = odr["PINMING_CD"].ToString();//品名代码
                    dr.PINMING_HANZI = odr["PINMING_HANZI"].ToString();//品名汉字
                    dr.SHIJI_STA = odr["SHIJI_STA"].ToString();//指示状态
                    dr.SHIJI_STA_HANZI = odr["SHIJI_STA_HANZI"].ToString();//指示状态汉字
                    dr.CHEPAI_NUM = odr["CHEPAI_NUM"].ToString(); //车牌号
                    dr.CHEZHU = odr["CHEZHU"].ToString();//车主
                    dr.CHEZHU_NAME = odr["CHEZHU_NAME"].ToString();  //车主汉字
                    dr.CHELIANG_XINGTAI = odr["CHELIANG_XINGTAI"].ToString();//车辆形态
                    dr.CHELIANG_XINGTAI_HANZI = odr["CHELIANG_XINGTAI_HANZI"].ToString();//车辆形态汉字
                    dr.HETONG_BIANHAO = odr["HETONG_BIANHAO"].ToString();//合同编号
                    dr.ONO = odr["ONO"].ToString();//ONO
                    dr.ZHUANGHUO = odr["ZHUANGHUO"].ToString();//装货地
                    dr.ZHUANGHUO_NAME = odr["ZHUANGHUO_NAME"].ToString(); //装货地略称
                    dr.XIEHUO = odr["XIEHUO"].ToString();//卸货地
                    dr.XIEHUO_NAME = odr["XIEHUO_NAME"].ToString(); //卸货地略称
                    dr.DIAODU_COUNTS = odr["DIAODU_COUNTS"].ToString(); //调度数
                    dr.DIAODU_WEIGHT = odr["DIAODU_WEIGHT"].ToString();//调度重量
                    dr.HETONG_YUNFEI = odr["HETONG_YUNFEI"].ToString(); //合同运费

                    tb.AddT_DIAODURow(dr);
                }
            }

            con.Close();
            return tb;
        }


    }
}